<?php
class CiucoursesController extends AppController {
	var $name = 'Ciucourses';
	var $components = array(
        'Auth'=>array(
        'userModel' => 'Ciuuser',
        'loginError' => "Usuario/Clave equivocada",
        'loginRedirect' => array('controller' => 'ciuusers', 'action' => 'home'),
        'autoRedirect' => false, /* evita la redireccion inmediata y le deja el control al metodo local */
        'loginAction' => array(
                  'controller' => 'ciuusers',
                  'action' => 'login',
                  'plugin' => false,
                 'admin' => false,
		)
		)
		, 'Session'
	);
	
	var $uses=array('Ciucourse','Ciuexam','Ciuacademicperiod');

	function index($language_id = 1 ) {
		$controller='Ciucourse';
		$key = "ciulanguage_id";
		$conditions=array();
		$conditions[$controller . "." . $key]= $language_id;
		$language_list=$this->Ciucourse->Ciulanguage->find('list');
		$this->paginate[$controller]['conditions'] = $conditions;
		$this->set('ciucourses', $this->paginate());
		$this->set('language_id',$language_id);
		$this->set('language_list',$language_list);
	}

	function indexcreate($plan_id = 1, $level_id = 1 ) {
		$controller='Ciucourse';
		if (!empty($this->data)) {
			$plan_id=$this->data['Ciucourse']['ciuplan_id'];
		}
		$conditions=array();
		$conditions[$controller . ".ciuacademicperiod_id"]= $this->getCurrentPeriod();
		$conditions["Ciusubject" . ".ciuplan_id"]= $plan_id;
		$this->paginate[$controller]['conditions'] = $conditions;
		$ciuplans = $this->Ciucourse->Ciusubject->Ciuplan->find('list');
		$this->set('ciuplans', $ciuplans );
		$this->set('ciucourses', $this->paginate());
	}

	//lista los cursos disponibles y en cuales esta inscrito para el usuario que esta conectado
	function listavailable( $ciuuser_id = null) {
		$controller='Ciucourse';
		$key = "ciusubject_id";
		//se verifica que este en el rango de fechas para preinscribirse
		$sqlFecha = "select DATE(preregistration_start) start,"
		. "DATE(preregistration_end) end "
		. "from ciuacademicperiods "
		. "where isactive=1 ";
		$fechaPara = $this->Ciucourse->query($sqlFecha);
		$startDate = $fechaPara['0']['0']['start'];
		$endDate   = $fechaPara['0']['0']['end'];

		$periodo       = $this->getCurrentPeriod();
		if($ciuuser_id == null){
			$ciuuser_id    = $this->getConnectedUser();
		}
		$subjectIdList = array();
		$owncourses    = array();

		if($ciuuser_id!=null && $ciuuser_id>0 ){
			$owncoursesRaw  = $this->Ciucourse->CiucourseCiustudent->find('all' , 
				array('conditions' => array('CiucourseCiustudent.ciuuser_id' => $ciuuser_id)) 
			);
			$owncourses     = $this->convertToIndexed($owncoursesRaw);
			$subjectIdList  = $this->getAvailableSubjects($ciuuser_id, $periodo);
		}
		$conditions=array();
		$conditions[$controller . "." . $key]= $subjectIdList;
		$conditions[]= "$controller.people_count <  $controller.capacity - 1";
		$conditions[]= " Ciuuser.ciustudentstate_id <> 3 ";
		
		
		$this->paginate[$controller]['conditions'] = $conditions;
		$ciucurspersstates=$this->Ciucourse->CiucourseCiustudent->Ciucurspersstate->find('list');
		$language_list=$this->Ciucourse->Ciulanguage->find('list');
		
		$this->set('ciucourses', $this->paginate());
		$this->set('posibles', $subjectIdList);
		$this->set('states', $ciucurspersstates);
		$this->set('language_list',$language_list);
		$this->set('owncourses', $owncourses);
		$this->set('startDate',$startDate);
		$this->set('endDate',$endDate);
	}	

	function getCurrentPeriod(){
		$result= $this->Ciucourse->query("select id  from ciuacademicperiods where isactive=1");
		return $result['0']['ciuacademicperiods']['id'];
	}
		
	function getConnectedUserData(){
		App::import('Component', 'Session');
		$mySession = new SessionComponent();
		$userInfo = $mySession->read('Auth.Ciuuser');
		$username=$userInfo['username'];
		return $this->Ciucourse->Ciuuser->findByUsername($username);
	}

	function getConnectedUser(){
		$ciuuserData = $this->getConnectedUserData();
		return $ciuuserData['Ciuuser']['id'];
	}

	function getAvailableSubjects($ciuuser_id, $periodo){
		//verifica si es estudiante antiguo o nuevo.
		$queryCursosAnteriores="select count(*) as qty from ciucourse_ciustudents where ciucurspersstate_id in (4, 5 ) and ciuuser_id=".$ciuuser_id;
		$cursosAnteriores=$this->Ciucourse->query($queryCursosAnteriores);
		$cantidadCursos=$cursosAnteriores[0][0]["qty"];
		//TODO: verificar la nota aprobatoria
		$notaMinima = 3;
		$queryavailable=
		"select id from ciusubjects where ( prerequisite=0 or prerequisite in 
		(select c.ciusubject_id 
		 from ciucourse_ciustudents e  
		 join ciucourses c  on (c.id=e.ciucourse_id) 
		 where c.ciuacademicperiod_id <> $periodo and e.total_score>=$notaMinima and e.ciuuser_id=". $ciuuser_id."))";
		$availableRaw=array();
		if( $cantidadCursos>0){
			$availableRaw=$this->Ciucourse->query($queryavailable);
		}
		return $this->convertToArray($availableRaw);
	}

	//el usuario se registra en un curso existente
	//TODO cambiar a dejarlo en un estado pendiente de asignacion a un curso...
	function preregister($course_id=1 ) {
		$ciuuser_id    = $this->getConnectedUser(); 
		$ciucourseData = $this->Ciucourse->findById($course_id);
		$ciusubject_id = $ciucourseData['Ciucourse']['ciusubject_id'];

		$registration  = array();
		$registration['ciucourse_id']       = $course_id;
		$registration['ciuuser_id']         = $ciuuser_id;
		$registration['ciusubject_id']      = $ciusubject_id;
		$registration['ciucurspersstate_id']= 1;  //preinscrito
		$registration['ciustudenttype_id']  = 1;  //estudiante unillanos
				
		if ($this->Ciucourse->CiucourseCiustudent->hasAny(array(
				'CiucourseCiustudent.ciucourse_id' => $course_id,
				'CiucourseCiustudent.ciuuser_id'   => $ciuuser_id
		))) {
			$this->Session->setFlash(__('You have been registered already', true));
		}else{
			$this->Ciucourse->CiucourseCiustudent->save($registration);
			$this->Session->setFlash(__('Registration succeded', true));
			$this->getPeopleCount($course_id);		
		}
		$this->redirect(array('action' => 'listavailable'));
	}

	function convertToArray($matrix){
		$result=array();
		foreach($matrix as $key => $arr1){
			$row=$arr1['ciusubjects']['id'];
			$result[]=$row;
		}
		return $result;
	}

	//crea un arreglo de cursos y los estados de los mismos para un usuario.
	function convertToIndexed($matrix){
		$result=array();
		foreach($matrix as $key => $arr1){
			$newkey=$arr1['Ciucourse']['id'];
			$newval=$arr1['CiucourseCiustudent']['ciucurspersstate_id'];
			if($newval!=null){
				$result[$newkey]=$newval;
			}
		}
		return $result;
	}

	function view($id = null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid ciucourse', true));
			$this->redirect(array('action' => 'index'));
		}
		$this->set('ciucourse', $this->Ciucourse->read(null, $id));
		$students=$this->Ciucourse->CiucourseCiustudent->find('all', array('order'=>'Ciuuser.document ASC',
		'conditions'=>array('CiucourseCiustudent.ciucourse_id'=>$id)
		));
                $listnumstudent= array();
                $totalstudent= array();
                $numstudent = "select count(*) as cant
                                        from ciucourses course
                                        join ciucourse_ciustudents course_student on (course.id=course_student.ciucourse_id)
                                        join ciuusers user on (course_student.ciuuser_id=user.id)
                                        where course.ciuacademicperiod_id=".$this->Ciuacademicperiod->getcurrent().
                                        " and course.id=".$id;
                 $listnumstudent  = $this->Ciucourse->query($numstudent);
                 $totalstudent=$listnumstudent[0][0]["cant"];
		$this->set('students', $students);
                $this->set('totalstudent', $totalstudent);
	}

	function add($language_id = null ) {
		//si hay un request, guardelo en la db.
		if (!empty($this->data)) {
			//recuerda el lenguaje
			$subject_id=$this->data['Ciucourse']['ciusubject_id'];
			$subjectData=$this->Ciucourse->Ciusubject->findById($subject_id);
			$language_id=$subjectData['Ciulanguage']['id'];
			$this->data['Ciucourse']['ciulanguage_id']=$language_id;
			$this->Ciucourse->create();
			//verificar si el curso ya esta registrado...
			$esDuplicado = $this->Ciucourse->isDuplicated($this->data['Ciucourse']);
			if($esDuplicado){
				$this->Session->setFlash(__('The ciucourse already exists.', true));
			}else{
				//se deja el docente pendiente. 2 es el id de la tabla ciuuser
				$this->data['Ciucourse']['ciuuser_id']=2;
				if ($this->Ciucourse->save($this->data)) {
					$this->Session->setFlash(__('The ciucourse has been saved', true));
					$this->redirect(array('action' => 'index', $language_id));
				} else {
					$this->Session->setFlash(__('The ciucourse could not be saved. Please, try again.', true));
				}
			}
		}
		$conditions=array();
		if(isset($language_id)){
			$conditions=array('Ciulanguage.id'=>$language_id);
		}
		//si no existe request, presente el form
		$ciulanguages = $this->Ciucourse->Ciulanguage->find('list'
		, array('conditions'=>$conditions)
		);
		$ciulevels = $this->Ciucourse->Ciulevel->find('list');
		$ciusubjects = $this->Ciucourse->Ciusubject->find('list');
		$ciutimetables = $this->Ciucourse->Ciutimetable->find('list');
		$ciurooms = $this->Ciucourse->Ciuroom->find('list');
		$ciuusers = $this->Ciucourse->Ciuuser->find('list',array('conditions'=>array('Ciuuser.ciuusertype_id'=>'2',array('Ciuuser.ciuuserstate_id'=>'1'))));
		//el curso queda en estado preinscrito
		$ciucoursestates = $this->Ciucourse->Ciucoursestate->find('list',
		array('conditions'=>array('Ciucoursestate.id'=> 1 ) ));
		//buscar el periodo actual...
		$current_period= $this->Ciucourse->Ciuacademicperiod->getCurrent();
		$ciuacademicperiods = $this->Ciucourse->Ciuacademicperiod->find('list',
		array('conditions' => array('Ciuacademicperiod.id'=>$current_period)) );
		$this->set(compact('ciulanguages', 'ciulevels', 'ciutimetables',
		'ciurooms', 'ciuusers', 'ciucoursestates', 'ciuacademicperiods','ciusubjects'
		));
	}

	function edit($id = null) {
		if (!$id && empty($this->data)) {
			$this->Session->setFlash(__('Invalid ciucourse', true));
			$this->redirect(array('action' => 'index'));
		}
		//guardar si hay un request...
		if (!empty($this->data)) {
                    
			if ($this->Ciucourse->save($this->data)) {
                            
				$this->Session->setFlash(__('The ciucourse has been saved', true));
                        $this->redirect(array('action' => 'indexbysubject', $data['Ciucourse']['ciusubject_id']));
			} else {
				$this->Session->setFlash(__('The ciucourse could not be saved. Please, try again.', true));
			}
		}
		//no hay request
		if (empty($this->data)) {
			$this->data = $this->Ciucourse->read(null, $id);
               }
                $ciusubjects = $this->Ciucourse->Ciusubject->find('list');
		$ciulanguages = $this->Ciucourse->Ciulanguage->find('list');
		$ciulevels = $this->Ciucourse->Ciulevel->find('list');
		$ciutimetables = $this->Ciucourse->Ciutimetable->find('list');
		$ciurooms = $this->Ciucourse->Ciuroom->find('list');
		$ciuusers = $this->Ciucourse->Ciuuser->find('list',array('conditions'=>array('Ciuuser.ciuusertype_id'=>'2',array('Ciuuser.ciuuserstate_id'=>'1'))));
		$ciucoursestates = $this->Ciucourse->Ciucoursestate->find('list');
		$ciuacademicperiods = $this->Ciucourse->Ciuacademicperiod->find('list');
               
		$this->set(compact('ciusubjects','ciulanguages', 'ciulevels', 'ciutimetables', 'ciurooms', 'ciuusers', 'ciucoursestates', 'ciuacademicperiods'));
	}

	function delete($id = null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid id for ciucourse', true));
			$this->redirect(array('action'=>'index'));
		}
		$rec = $this->Ciucourse->findById($id);
		if(isset($rec)){
			$language_id=$rec['Ciucourse']['ciulanguage_id'];
		}
		if($this->Ciucourse->isOkToErase($id)){
			if ($this->Ciucourse->delete($id)) {
				$this->Session->setFlash(__('Ciucourse deleted', true));
				$this->redirect(array('action'=>'index', $language_id));
			}
		}else{
			$this->Session->setFlash(__('Ciucourse was not empty', true));
		}
		$this->redirect(array('action' => 'index'));
	}

	function indexAvailable($ciuuser_id=1){
		$controller='Ciucourse';
		$key = "ciulanguage_id";
		$conditions=array();
		$conditions[$controller . "." . $key]= $language_id;
		$language_list=$this->Ciucourse->Ciulanguage->find('list');
		$this->paginate[$controller]['conditions'] = $conditions;
		$this->set('ciucourses', $this->paginate());
		$this->set('language_id',$language_id);
		$this->set('language_list',$language_list);
	}

	function indexapprove() {
		$this->Ciucourses->recursive = 0;
		$this->set('ciucourses', $this->paginate());
	}

	function editapprove($id = null) {
		if (!$id && empty($this->data)) {
			$this->Session->setFlash(__('Invalid ciucourse', true));
			$this->redirect(array('action' => 'indexapprove'));
		}
		if (!empty($this->data)) {
			if ($this->Ciucourse->save($this->data)) {
				$this->Session->setFlash(__('The ciucourse has been saved', true));
				$this->redirect(array('action' => 'indexapprove'));
			} else {
				$this->Session->setFlash(__('The ciucourse could not be saved. Please, try again.', true));
			}
		}
		if (empty($this->data)) {
			$this->data = $this->Ciucourse->read(null, $id);
		}
		$ciulanguages = $this->Ciucourse->Ciulanguage->find('list');
		$ciulevels = $this->Ciucourse->Ciulevel->find('list');
		$ciutimetables = $this->Ciucourse->Ciutimetable->find('list');
		$ciurooms = $this->Ciucourse->Ciuroom->find('list');
		$ciuusers = $this->Ciucourse->Ciuuser->find('list',array('conditions'=>array('Ciuuser.ciuusertype_id'=>'2')));
		$ciucoursestates = $this->Ciucourse->Ciucoursestate->find('list',array('conditions'=>array('Ciucoursestate.id !=' =>'4')));
		$ciuacademicperiods = $this->Ciucourse->Ciuacademicperiod->find('list');
		$this->set(compact('ciulanguages', 'ciulevels', 'ciutimetables', 'ciurooms', 'ciuusers', 'ciucoursestates', 'ciuacademicperiods'));
	}
	
	
	function browse($language_id = 1 ) {
		$controller='Ciucourse';
		$key = "ciulanguage_id";
		$conditions=array();
		$conditions[$controller . "." . $key]= $language_id;
		$language_list=$this->Ciucourse->Ciulanguage->find('list');
		$this->paginate[$controller]['conditions'] = $conditions;
		$this->set('ciucourses', $this->paginate());
		$this->set('language_id',$language_id);
		$this->set('language_list',$language_list);
	}

	//crea un curso en un horario, plan y nivel... y le asigna usuarios
	function create($timetable_id = null, $plan_id=null,  $subject_id =null) {
		//Parametrizacion de estudiantes minimo y maximo por curso
                 $sqlmax= "select definition from ciuparameters where name='MAX_ESTUDIANTES_POR_CURSO'";
                 $sqlmin= "select definition from ciuparameters where name='MAX_ESTUDIANTES_POR_CURSO'";
                 $max=$this->Ciucourse->query($sqlmax);
                 $min=$this->Ciucourse->query($sqlmin);
            
                 $limiteInferior=$min['0']['ciuparameters']['definition']; 
		 $limiteSuperior=$max['0']['ciuparameters']['definition'];
          
		if (!$timetable_id) {
			$this->Session->setFlash(__('Invalid ciutimetable', true));
			$this->redirect(array('action' => 'index'));
		}
		$condicionCreacion = "verified=1 and ciuuserstate_id=1 and ciuplan_id=".$plan_id." and ciutimetable_id=".$timetable_id. " and ciuadmisionstate_id=3 ";
		//encuentra la cantidad de estudiantes admitidos.
		$sql=" select count(*) as qty from ciuusers where " . $condicionCreacion ;
		$result=$this->Ciucourse->query($sql);
		$cantidadEstudiantes=$result[0][0]["qty"];
		if($cantidadEstudiantes < $limiteInferior){
			$this->Session->setFlash("Existen solo $cantidadEstudiantes  para crear el curso, se requieren $limiteInferior estudiantes.");
			$this->redirect(array('controller'=>'ciuusers','action' => 'showadmitted'));
		}
		//arreglo para ser guardado en cursos
		$dataCurso=array();
		$dataCurso['ciutimetable_id'] = $timetable_id;
		$dataCurso['ciuplan_id']  = $plan_id;
		$dataCurso['ciusubject_id'] = $subject_id;
		$dataCurso['ciuacademicperiod_id']= $this->Ciuacademicperiod->getcurrent();

		//Busca la asignatura que tenga asociado el nivel del curso.
		$dataAsignatura = $this->Ciucourse->Ciusubject->find('first',array('conditions'=>array( 'Ciusubject.id'=>$subject_id)));
		$dataCurso['ciulanguage_id']   = $dataAsignatura['Ciusubject']['ciulanguage_id'];
		$dataCurso['ciusubject_id']    = $subject_id;
		$dataCurso['ciucoursestate_id'] = 2;
		//adiciona el registro de curso
		$courseId=0;
		if ($this->Ciucourse->save($dataCurso)) {
			$courseId = $this->Ciucourse->getLastInsertID();
			$this->Session->setFlash("El curso fue creado exitosamente.");
			$this->addStudents($courseId, $subject_id, $timetable_id, $plan_id );
			$this->redirect(array('action'=>'view', $courseId));
		} else {
			$this->Session->setFlash("No se pudo crear el curso.");
		}
	}

	function addStudents($courseId, $subject_id, $timetable_id, $plan_id ){
		// parametro estudiantes maximo por curso
		$sqlmax= "select definition from ciuparameters where name='MAX_ESTUDIANTES_POR_CURSO'";
		$max=$this->Ciucourse->query($sqlmax);
		$capacity=$max['0']['ciuparameters']['definition'];
		//adicionar los usuarios admitidos a este nuevo curso.
            
		if ( $courseId>0 ) {
			//para asignar un estudiante a un curso debe estar en el plan, horario y haber sido admitido.
			//busca las id de los estudiantes admitidos por plan.
			$uidssql = "select u.id from ciuexam_ciuusers d
			join ciuexams e on (e.id=d.ciuexam_id)
			join ciuusers u on (u.id=d.ciuuser_id)
			where u.ciutimetable_id=". $timetable_id .
			" and u.ciuplan_id=". $plan_id .
			" and d.ciusubject_id=". $subject_id .
			" and u.ciuadmisionstate_id=3".
                        " and u.verified=1
                         and e.ciuacademicperiod_id=".$this->Ciuacademicperiod->getcurrent(); //periodo academico actual
			$rawuids = $this->Ciucourse->query($uidssql);
			$uids=array();
			foreach($rawuids as $key => $val){
				$uids[]=$val['u']['id'];
			}
			//adiciona los estudiantes al nuevo curso
			$insert="insert into ciucourse_ciustudents(ciucourse_id, ciuuser_id, ciusubject_id, ciustudenttype_id, ciucurspersstate_id) ".
			" select $courseId, id, ". $subject_id .", ciustudenttype_id, 2  ".
			" from ciuusers where id in ( ". implode($uids, ",") ." ) limit 0, $capacity";
			$this->Ciucourse->query($insert);
			//deja los estudiantes pendientes de matricula
			$update=" update ciuusers set ciuadmisionstate_id = 6 ".
			" where id in (select ciuuser_id from ciucourse_ciustudents where ciucourse_id=$courseId) ";
			$updexamuser=" update ciuexam_ciuusers set ciuadmisionstate_id = 6 ".
			" where ciuuser_id in (select ciuuser_id from ciucourse_ciustudents where ciucourse_id=$courseId) ";                       		
			$this->getPeopleCount($courseId);
			$this->Ciucourse->query($update);
                        $this->Ciucourse->query($updexamuser);
		}
	}

	function createadvanced($subject_id = null ) {
		//si hay un request, guardelo en la db.
		if (!empty($this->data)) {
			$subject_id=0;
			if( isset($this->data['Ciucourse']['ciusubject_id'])){
				$subject_id=$this->data['Ciucourse']['ciusubject_id'];
			}
			$subjectData=$this->Ciucourse->Ciusubject->findById($subject_id);
			$language_id=$subjectData['Ciulanguage']['id'];
			$this->data['Ciucourse']['ciulanguage_id']=$language_id;
			
			$this->Ciucourse->create();
			$esDuplicado = $this->Ciucourse->isDuplicated($this->data['Ciucourse']);
			if($esDuplicado){
				$this->Session->setFlash(__('The ciucourse already exists.', true));
			}else{
				//se deja el docente pendiente. 2 es el id de la tabla ciuuser
				$this->data['Ciucourse']['ciuuser_id'] = 2;
				//TODO traer la capacidad de parametros...
				$this->data['Ciucourse']['capacity'] = 20;
				
				if ($this->Ciucourse->save($this->data)) {
					$this->Session->setFlash(__('The ciucourse has been saved', true));
					$this->redirect(array('action' => 'indexsummary' ));
				} else {
					$this->Session->setFlash(__('The ciucourse could not be saved. Please, try again.', true));
				}
			}
		}
		$conditions=array();
		$subjectData=array();
		$language_id=0;
		if(isset($subject_id)){
			$subjectData=$this->Ciucourse->Ciusubject->findById($subject_id);
			$conditions=array('Ciulanguage.id'=>$subjectData['Ciusubject']['ciulanguage_id']);
		}
		$ciulanguages = $this->Ciucourse->Ciulanguage->find('list', array('conditions'=>$conditions));
		$ciulevels = $this->Ciucourse->Ciulevel->find('list');

		$subjectCondition=array();
		if( $subject_id != null ){
			$subjectCondition = array('Ciusubject.id'=>$subject_id);
		}

		$ciusubjects = $this->Ciucourse->Ciusubject->find('list', array('conditions'=> $subjectCondition  ));
		$ciutimetables = $this->Ciucourse->Ciutimetable->find('list');
		$ciurooms = $this->Ciucourse->Ciuroom->find('list');
		$ciuusers = $this->Ciucourse->Ciuuser->find('list',array('conditions'=>array('Ciuuser.ciuusertype_id'=>'2',array('Ciuuser.ciuuserstate_id'=>'1'))));
		//el curso queda en estado preinscrito
		$ciucoursestates = $this->Ciucourse->Ciucoursestate->find('list', array('conditions'=>array('Ciucoursestate.id'=> 1 ) ));
		$current_period= $this->Ciucourse->Ciuacademicperiod->getCurrent();
		$ciuacademicperiods = $this->Ciucourse->Ciuacademicperiod->find('list',
		array('conditions' => array('Ciuacademicperiod.id'=>$current_period)) );
		
		$this->set(compact('ciulanguages', 'ciulevels', 'ciutimetables',
		'ciurooms', 'ciuusers', 'ciucoursestates', 'ciuacademicperiods','ciusubjects'
		));
	}

	function indexsummary(){
		$queryAdvanced = "select Ciuplan.id,Ciuplan.name,Ciusubject.sequence,Ciusubject.id, Ciusubject.name, count(Ciucourse.id) as qty, sum(Ciucourse.people_count) as people
                         from ciuplans Ciuplan 
                         join ciusubjects Ciusubject on (Ciuplan.id=Ciusubject.ciuplan_id) 
                         left join ciucourses Ciucourse on (Ciucourse.ciusubject_id = Ciusubject.id)
                         where  ciuacademicperiod_id=".$this->Ciuacademicperiod->getcurrent().
		         " group by Ciuplan.id,Ciuplan.name,Ciusubject.sequence,Ciusubject.id, Ciusubject.name
                         order by 1,2,3";
		$listadvanced = $this->Ciucourse->query($queryAdvanced);
		$this->set('listadvanced',$listadvanced);
	}

	function indexbysubject($subject_id = null){
		$controller='Ciucourse';
		$key = "ciusubject_id";
                $key2= "ciuacademicperiod_id";
		$conditions=array();
		$subject_name="";
		if(isset($subject_id)){
			$conditions[$controller . "." . $key]= $subject_id;
                        $conditions[$controller . "." . $key2]= $this->Ciuacademicperiod->getcurrent();
			$subjectData=$this->Ciucourse->Ciusubject->findById($subject_id);
			$subject_name = $subjectData['Ciusubject']['name'];
		}
		$language_list=$this->Ciucourse->Ciulanguage->find('list');
		$this->paginate[$controller]['conditions'] = $conditions;
		$this->set('ciucourses', $this->paginate());
		$this->set('subject_id',$subject_id);
		$this->set('subject_name',$subject_name);
		$this->set('language_list',$language_list);
	}
	
    function getPeopleCount($id=null){
		$result=0;
		if ($id!=null) {
			$data = $this->Ciucourse->read(null, $id);	
			$sql=" select count(*) as qty from ciucourse_ciustudents where ciucourse_id=" .$id  ;
			$raw=$this->Ciucourse->query($sql);
			$result=$raw[0][0]["qty"];
			if($result>0){
				$data['Ciucourse']['people_count']=$result;
				$this->Ciucourse->save($data['Ciucourse']);
			}
		}
		return $result;
	}
	
    function listcurrent(){
        $ciuuser_id    = $this->getConnectedUser();
		$sql="select Ciucourse.id, Ciusubject.name, Ciutimetable.name, Ciuroom.name, Ciuuser.first_names, Ciuuser.last_names, Ciucurspersstate.name
		 from ciucourses Ciucourse 
                 join ciucourse_ciustudents CiucourseCiustudent on (Ciucourse.id=CiucourseCiustudent.ciucourse_id)
		 join ciucurspersstates Ciucurspersstate on (CiucourseCiustudent.ciucurspersstate_id=Ciucurspersstate.id)
                 join ciurooms Ciuroom on (Ciucourse.ciuroom_id=Ciuroom.id)
		 join ciuusers Ciuuser on (Ciuuser.id=Ciucourse.ciuuser_id)  
		 join ciutimetables Ciutimetable on (Ciucourse.ciutimetable_id=Ciutimetable.id) 
		 join ciusubjects Ciusubject on (Ciucourse.ciusubject_id=Ciusubject.id)
		 where Ciucourse.ciuacademicperiod_id in ( select id from ciuacademicperiods where isactive=1)
                    and CiucourseCiustudent.ciuuser_id =   $ciuuser_id
                    group by Ciutimetable.id, Ciutimetable.name,  Ciusubject.id, Ciusubject.name ";
		$ciucourses= $this->Ciucourse->query($sql);
		
        $this->set('ciucourses', $ciucourses);
	}
}
